package com.mbbmap.app.home;

import java.util.*;
import java.sql.*;

import com.mbbmap.app.manager.*;
import com.mbbmap.security.dao.*;
import com.mbbmap.util.*;

import java.text.SimpleDateFormat;

public class SecAccessHome {
        private static SecAccessHome instance = null;

                /**
                 * Returns the single instance, creating one if it's the
                 * first time this method is called.
                 *
                 * @return IslamicContractHome. The single instance.
                 */
        public static SecAccessHome getInstance() {
                //thread-safe implementation
                if (instance == null) {
                        synchronized (SecAccessHome.class) {
                                if (instance == null) {
                                        instance = new SecAccessHome();
                                }
                        }
                }
                return instance;
        }

        private SecAccessHome() {
                super();
        }


        /**
         *
         */
        public ArrayList findSecGroupAccessList(String strMerchant, String strSelectedGroup) {
                                //System.out.println("********************"+newIcNo);

                ArrayList secGroupAccessList = new ArrayList();

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecAccessHome_findSecGroupAccessList_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{

                                StringBuffer sqlStmt = new StringBuffer("");

                                //sqlStmt.append("select acct_name, is_contract,psr_rate from tblislamic_contract where acct_name='"+accountName+"'");
                                //sqlStmt.append("select role_code,module_code, read_flag,update_flag,write_flag,created_by, created_date from TBLROLEPERMISSION where mer_name = '"+strMerchant+"' AND role_code ='" + strSelectedGroup + "'");
                                sqlStmt.append("select role_code,module_code, read_flag,update_flag,write_flag,created_by, created_date from TBLROLEPERMISSION where mer_name = '"+strMerchant+"' AND role_code ='" + strSelectedGroup + "'"+" order by module_code");
                                
                                System.out.println("SecAccessHome_findSecGroupAccessList_002 : " + sqlStmt);

                                stmt = conn.prepareStatement(sqlStmt.toString());
                                rs = stmt.executeQuery();
                                //System.out.println("Resultset . next()");

                                while (rs.next()){
                                                SecAccessDao secaccessDao = new SecAccessDao();
                                                String sgroupCode = rs.getString(1);
                                                if (sgroupCode != null){
                                                        sgroupCode = sgroupCode.trim();
                                                }
                                                String smoduleCode = rs.getString(2);
                                                if (smoduleCode != null){
                                                        smoduleCode = smoduleCode.trim();
                                                }
                                                String sread_flag = rs.getString(3);
                                                if (sread_flag != null){
                                                        sread_flag = sread_flag.trim();
                                                }
                                                String supdate_flag = rs.getString(4);
                                                if (supdate_flag != null){
                                                        supdate_flag = supdate_flag.trim();
                                                }
                                                String swrite_flag = rs.getString(5);
                                                if (swrite_flag != null){
                                                        swrite_flag = swrite_flag.trim();
                                                }
                                                String screatedby = rs.getString(6);
                                                if (screatedby != null){
                                                        screatedby = screatedby.trim();
                                                }
                                                
                                                java.util.Date dCreateddt = rs.getDate(7);
                                                String screateddt = "";
                                                if (dCreateddt != null){
                                                        screateddt = DateUtil.formatLocalSlash(dCreateddt).trim();                                                  
                                                }
                                //System.out.println("TradeAcctHome.java findTradeAcctList client_code"+client_code);
                                                secaccessDao.set(sgroupCode,smoduleCode,sread_flag,supdate_flag,swrite_flag,screatedby,screateddt);
                                                secGroupAccessList.add(secaccessDao);
                                } //while (rs.next())
                                rs.close();
                                stmt.close();

                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("SecAccessHome_findTradeAcct_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("SecAccessHome_findTradeAcct_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }

                                //System.out.println("Resultset12 . next()");
                return secGroupAccessList;
        }

        /**
         *      This is to Add Security Group & Module Access Rights
         *      Author  :       Michael Kang
         *      Date    :       10/02/2009
         */
        public String addSecGroupAccessList(String strMerchant,String strGroupCode,String strModuleCode,String strReadFlag,String strUpdateFlag,String strWriteFlag, String strLoginID) {

                String strReturn = "N";

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecAccessHome_addSecGroupAccessList_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{
                                StringBuffer sqlStmt = new StringBuffer("");
                                if (strModuleCode != null && strLoginID != null && strMerchant != null){
                                        //NRIC,PAYEE_CODE,BILLER_CODE,ACCT_NO,PAYMENT_STATUS,BILL_AMT,PAY_AMT,BILL_DATE,DUE_DATE,
                                        java.util.Date creationDate = new java.util.Date();
                                        //String strCreationDate = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(creationDate);
                                        //System.out.println("BillAcctHome.java updatePayHistory strActionDate" + strActionDate);
                                        //sqlStmt.append("select a.biller_code, a.payee_code,a.acct_no,a.bill_ref1,a.status,a.act_flag,a.deact_flag, b.biller_name from tblbills a inner join tblbiller b on a.biller_code = b.biller_code where deact_flag <> '2' AND nric='"+newIcNo+"'");
                                        //sqlStmt.append("INSERT INTO TBLROLEPERMISSION (MER_NAME,ROLE_TYPE,ROLE_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG,CREATED_BY,CREATED_DATE) VALUES ('" + strMerchant + "','00','" + strGroupCode + "','" + strModuleCode + "','" + strReadFlag + "','" + strUpdateFlag + "','" + strWriteFlag + "','" + strLoginID + "',to_date('"+strCreationDate+"','dd/MM/yyyy HH24:MI:SS'))");
                                        sqlStmt.append("INSERT INTO TBLROLEPERMISSION (MER_NAME,ROLE_TYPE,ROLE_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG,CREATED_BY,CREATED_DATE) VALUES ('" + strMerchant + "','00','" + strGroupCode + "','" + strModuleCode + "','" + strReadFlag + "','" + strUpdateFlag + "','" + strWriteFlag + "','" + strLoginID + "',?)");
                                        //System.out.println("SecAccessHome_addSecGroupAccessList_001 sqlStmt.toString()" + sqlStmt.toString());
                                        //stmt = conn.prepareStatement(sqlStmt.toString());
                                        PreparedStatement insertPstmt = null;

                                        insertPstmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt.setTimestamp(1, new java.sql.Timestamp(creationDate.getTime()));
                                        
                                        insertPstmt.executeUpdate();
                                        insertPstmt.close();
                                        strReturn = "Y";
                                        //System.out.println("BillActHome Resultset . updatePayHistory()");
                                }else{
                                        strReturn = "N";
                                } // End if all parameters not null
                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("SecAccessHome_addSecGroupAccessList_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("SecAccessHome_addSecGroupAccessList_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }
                return strReturn;
        }



        /**
         *      This is to Remove Security Group & Module Access Rights
         *      Author  :       Michael Kang
         *      Date    :       10/02/2009
         */
        public String remSecGroupAccess(String strMerchant,String strGroupCode,String strModuleCode, String strLoginID) {

                String strReturn = "N";

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecAccessHome_remSecGroupAccess_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{
                                StringBuffer sqlStmt = new StringBuffer("");
                                if (strModuleCode != null && strLoginID != null && strMerchant != null){
                                        //NRIC,PAYEE_CODE,BILLER_CODE,ACCT_NO,PAYMENT_STATUS,BILL_AMT,PAY_AMT,BILL_DATE,DUE_DATE,
                                        java.util.Date creationDate = new java.util.Date();
                                        String strCreationDate = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(creationDate);
                                        //System.out.println("BillAcctHome.java updatePayHistory strActionDate" + strActionDate);
                                        //sqlStmt.append("select a.biller_code, a.payee_code,a.acct_no,a.bill_ref1,a.status,a.act_flag,a.deact_flag, b.biller_name from tblbills a inner join tblbiller b on a.biller_code = b.biller_code where deact_flag <> '2' AND nric='"+newIcNo+"'");
                                        sqlStmt.append("DELETE FROM TBLROLEPERMISSION WHERE MER_NAME = '" + strMerchant + "' AND ROLE_TYPE = '00' AND ROLE_CODE = '" + strGroupCode + "' AND MODULE_CODE = '" + strModuleCode + "'");
                                        //System.out.println("SecAccessHome_remSecGroupAccess_001 sqlStmt.toString()" + sqlStmt.toString());
                                        //stmt = conn.prepareStatement(sqlStmt.toString());
                                        PreparedStatement insertPstmt = null;

                                        insertPstmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt.executeUpdate();
                                        insertPstmt.close();
                                        strReturn = "Y";
                                        //System.out.println("BillActHome Resultset . updatePayHistory()");
                                }else{
                                        strReturn = "N";
                                } // End if all parameters not null
                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("SecAccessHome_remSecGroupAccess_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("SecAccessHome_remSecGroupAccess_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }
                return strReturn;
        }

        /**
         *      This is to Get Security Group Level
         *      Author  :       Michael Kang
         *      Date    :       13/02/2009
         */
        public ArrayList findSecGroupLevelList(String strMerchant, String strSelectedGroup) {
                                //System.out.println("********************"+newIcNo);

                ArrayList secGroupLevelList = new ArrayList();

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecAccessHome_findSecGroupLevelList_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{

                                StringBuffer sqlStmt = new StringBuffer("");

                                //sqlStmt.append("select acct_name, is_contract,psr_rate from tblislamic_contract where acct_name='"+accountName+"'");
                                sqlStmt.append("select parent_id,child_id,created_by, created_date from TBLTREE where mer_name = '"+strMerchant+"' AND parent_id = '" + strSelectedGroup + "' AND tree_type ='" + Constants.TREE_TYPE_ACCESS + "'");
                                System.out.println("SecAccessHome_findSecGroupLevelList_002 : " + sqlStmt);

                                stmt = conn.prepareStatement(sqlStmt.toString());

                                rs = stmt.executeQuery();
                                //System.out.println("Resultset . next()");

                                while (rs.next()){

                                                SecGroupLevelDao secGroupLevelDao = new SecGroupLevelDao();

                                                String sgroupCode = rs.getString(1);
                                                if (sgroupCode != null){
                                                        sgroupCode = sgroupCode.trim();
                                                }
                                                String sgroupLevel = rs.getString(2);
                                                if (sgroupLevel != null){
                                                        sgroupLevel = sgroupLevel.trim();
                                                }
                                                String screatedby = rs.getString(3);
                                                if (screatedby != null){
                                                        screatedby = screatedby.trim();
                                                }
                                                
                                                java.util.Date dCreateddt = rs.getDate(4);
                                                String screateddt = "";
                                                if (dCreateddt != null){
                                                        screateddt = DateUtil.formatLocalSlash(dCreateddt).trim();                                                  
                                                }
                                                
                                //System.out.println("TradeAcctHome.java findTradeAcctList client_code"+client_code);
                                                secGroupLevelDao.set(sgroupCode,sgroupLevel,screatedby,screateddt);
                                                secGroupLevelList.add(secGroupLevelDao);
                                } //while (rs.next())
                                rs.close();
                                stmt.close();

                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("SecAccessHome_findTradeAcct_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("SecAccessHome_findTradeAcct_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }

                                //System.out.println("Resultset12 . next()");
                return secGroupLevelList;
        }

        /**
         *      This is to Add Security Group Level
         *      Author  :       Michael Kang
         *      Date    :       10/02/2009
         */
        public String addSecGroupLevel(String strMerchant,String strGroupCode,String strGroupLevel, String strLoginID) {

                String strReturn = "N";

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecAccessHome_addSecGroupLevel_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{
                                StringBuffer sqlStmt = new StringBuffer("");
                                if (strGroupCode != null && strGroupLevel != null && strLoginID != null && strMerchant != null){
                                        //NRIC,PAYEE_CODE,BILLER_CODE,ACCT_NO,PAYMENT_STATUS,BILL_AMT,PAY_AMT,BILL_DATE,DUE_DATE,
                                        java.util.Date creationDate = new java.util.Date();
                                        //String strCreationDate = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(creationDate);
                                        //System.out.println("BillAcctHome.java updatePayHistory strActionDate" + strActionDate);
                                        //sqlStmt.append("select a.biller_code, a.payee_code,a.acct_no,a.bill_ref1,a.status,a.act_flag,a.deact_flag, b.biller_name from tblbills a inner join tblbiller b on a.biller_code = b.biller_code where deact_flag <> '2' AND nric='"+newIcNo+"'");
                                        //sqlStmt.append("INSERT INTO TBLTREE (MER_NAME,TREE_TYPE,PARENT_ID,CHILD_ID,CREATED_BY,CREATED_DATE) VALUES ('" + strMerchant + "','" + Constants.TREE_TYPE_ACCESS + "','" + strGroupCode + "','" + strGroupLevel + "','" + strLoginID + "',to_date('"+strCreationDate+"','dd/MM/yyyy HH24:MI:SS'))");
                                        sqlStmt.append("INSERT INTO TBLTREE (MER_NAME,TREE_TYPE,PARENT_ID,CHILD_ID,CREATED_BY,CREATED_DATE) VALUES ('" + strMerchant + "','" + Constants.TREE_TYPE_ACCESS + "','" + strGroupCode + "','" + strGroupLevel + "','" + strLoginID + "',? )");
                                        //System.out.println("SecAccessHome_addSecGroupAccessList_001 sqlStmt.toString()" + sqlStmt.toString());
                                        //stmt = conn.prepareStatement(sqlStmt.toString());
                                        PreparedStatement insertPstmt = null;

                                        insertPstmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt.setTimestamp(1, new java.sql.Timestamp(creationDate.getTime()));
                                        insertPstmt.executeUpdate();
                                        insertPstmt.close();
                                        strReturn = "Y";
                                }else{
                                        strReturn = "N";
                                } // End if all parameters not null
                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("SecAccessHome_addSecGroupLevel_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("SecAccessHome_addSecGroupLevel_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }
                return strReturn;
        }



        /**
         *      This is to Remove Group Level
         *      Author  :       Michael Kang
         *      Date    :       10/02/2009
         */
        public String remSecGroupLevel(String strMerchant,String strGroupCode,String strGroupLevel, String strLoginID) {

                String strReturn = "N";

                int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
                DBConnectionManager connMgr;
                String connectionPool;
                SystemLogManager sysLogMgr;

                connectionPool = ConfigManager.getInstance().getConnectionPool();
                connMgr = DBConnectionManager.getInstance();
                String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
                if (timeOutValue != null) {
                        try {
                                TIMEOUT = Integer.parseInt(timeOutValue);
                        } catch (NumberFormatException e) {}
                }
                sysLogMgr = SystemLogManager.getInstance();

                Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

                PreparedStatement stmt = null;
                ResultSet rs= null;

                try {
                        if (conn == null) {
                                EnhancedException ex = new EnhancedException("");
                                ex.setExceptionLocation("SecAccessHome_remGroupLevel_001 [Connection at : " + connectionPool + "]");
                                ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
                                sysLogMgr.logError(ex);
                        } //if (conn == null)
                        else{
                                StringBuffer sqlStmt = new StringBuffer("");
                                if (strGroupCode != null && strGroupLevel != null && strLoginID != null && strMerchant != null){
                                        //NRIC,PAYEE_CODE,BILLER_CODE,ACCT_NO,PAYMENT_STATUS,BILL_AMT,PAY_AMT,BILL_DATE,DUE_DATE,
                                        java.util.Date creationDate = new java.util.Date();
                                        String strCreationDate = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(creationDate);
                                        //System.out.println("BillAcctHome.java updatePayHistory strActionDate" + strActionDate);
                                        //sqlStmt.append("select a.biller_code, a.payee_code,a.acct_no,a.bill_ref1,a.status,a.act_flag,a.deact_flag, b.biller_name from tblbills a inner join tblbiller b on a.biller_code = b.biller_code where deact_flag <> '2' AND nric='"+newIcNo+"'");
                                        sqlStmt.append("DELETE FROM TBLTREE WHERE MER_NAME = '" + strMerchant + "' AND TREE_TYPE = '" + Constants.TREE_TYPE_ACCESS + "' AND PARENT_ID = '" + strGroupCode + "' AND CHILD_ID = '" + strGroupLevel + "'");
                                        //System.out.println("SecAccessHome_remGroupLevel_001 sqlStmt.toString()" + sqlStmt.toString());
                                        //stmt = conn.prepareStatement(sqlStmt.toString());
                                        PreparedStatement insertPstmt = null;

                                        insertPstmt = conn.prepareStatement(sqlStmt.toString());
                                        insertPstmt.executeUpdate();
                                        insertPstmt.close();
                                        strReturn = "Y";
                                        //System.out.println("BillActHome Resultset . updatePayHistory()");
                                }else{
                                        strReturn = "N";
                                } // End if all parameters not null
                        } //[ELSE] if (conn == null)
                } catch (Exception e) {
                        EnhancedException ex = new EnhancedException(e.toString());
                        ex.setExceptionLocation("SecAccessHome_remGroupLevel_002 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Exception Encountered");
                        sysLogMgr.logError(ex);
                } catch (Throwable t) {
                        EnhancedException ex = new EnhancedException(t.toString());
                        ex.setExceptionLocation("SecAccessHome_remGroupLevel_003 [Connection at : " + connectionPool + "]");
                        ex.setExtraMessage("Throwable Encountered");
                        sysLogMgr.logError(ex);
                } finally {
                        if (stmt != null) {
                                try {
                                        stmt.close();
                                } catch (SQLException e1) {}
                        }
                        connMgr.freeConnection(connectionPool, conn);
                }
                return strReturn;
        }
}

